
DROP PROCEDURE IF EXISTS candidate_filter_edu;

DELIMITER //
CREATE PROCEDURE `candidate_filter_edu` (
    IN eduLevel INT, IN eduLevelImp INT)
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER

BEGIN
    -- MAXIMUM SCORE IS 10 and MINIMUM SCORE IS 0.
    IF eduLevelImp = 10 THEN
        SELECT 
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            canEdu.score AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_education AS canEdu
        WHERE
            can.is_deleted = 0
            AND canEdu.candidate_id = can.id
            AND canEdu.education_id = eduLevel;
    ELSE
        SELECT 
            can.id AS col1, 
            CONCAT (can.last_name, ' ', can.middle_name, ' ', can.first_name) AS col2,
            can.email AS col3, 
            can.contact_number AS col4, 
            canEdu.score * eduLevelImp / 10 AS col5
        FROM
            hrentry_db.hre_job_candidate AS can,
            hrentry_db.hre_candidate_education AS canEdu
        WHERE
            can.is_deleted = 0
            AND canEdu.candidate_id = can.id
            AND canEdu.education_id = eduLevel;
    END IF;
END//

